package com.netcracker.util.dao.impl;

import com.netcracker.db.exception.DBException;
import com.netcracker.db.DBManager;
import com.netcracker.entity.Right;
import com.netcracker.entity.record.UserRecord;
import com.netcracker.util.dao.UserDAO;
import com.netcracker.entity.User;
import com.netcracker.util.dao.DAOFacade;
import com.netcracker.util.dao.closer.DAOCloseHelper;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UserDAOImpl extends DAOCloseHelper implements UserDAO {
    
    protected static final String USER_ID_VALUE = "UserId";
    protected static final String USER_RIGHT_VALUE = "UserRight";
    protected static final String USER_NAME_VALUE = "FullName";
    protected static final String USER_BIRTHDAY_VALUE = "DateOfBirth";
    protected static final String USER_LOGIN_VALUE = "Login";
    protected static final String USER_PASSWORD_VALUE = "Password";
    protected static final String USER_REGISTER_VALUE = "RegisterDate";
    protected static final String USER_COUNTRY_VALUE = "CountryId";

    protected static final String CREATE_USER_SQL = "INSERT INTO USERS VALUES(UserSequence.NEXTVAL, ?, ?, ?, ?, ?, ?, ?)";
    protected static final String UPDATE_USER_SQL = "UPDATE USERS SET FullName = ?, DateOfBirth = ?, "
            + " Password = ?, RegisterDate = ?, CountryId = ? WHERE UserId = ?";
    protected static final String REMOVE_USER_SQL = "DELETE FROM Users WHERE UserId = ?";
    protected static final String FIND_USER_BY_ID_SQL = "SELECT * FROM USERS WHERE UserId = ?";
    protected static final String FIND_ALL_USERS_SQL = "SELECT * FROM USERS";
    protected static final String FIND_USER_BY_LOGIN_SQL = "SELECT * FROM USERS WHERE Login = ?";
    protected static final String FIND_COUNT_USERS_SQL = "SELECT COUNT(*) FROM USERS";
    protected static final String CHANGE_PASSWORD = "UPDATE USERS SET Password = ? WHERE UserId = ?";
    
    private DBManager dbManager;
    private DAOFacade daoFacade;
    
    public UserDAOImpl(DBManager dbManager, DAOFacade daoFacade) {
        this.dbManager = dbManager;
        this.daoFacade = daoFacade;
    }
        
    @Override
    public void setDAOFacade(DAOFacade daoFacade) {
        this.daoFacade = daoFacade;
    }
    
    @Override
    public void setDBManager(DBManager dbManager) {
        this.dbManager = dbManager;
    }
    
    @Override
    public void create(UserRecord user) throws DBException {
        PreparedStatement preparedStatement = null;
        String right = getStringRight(user.getRight());
        try
        {
            dbManager.commit();
            preparedStatement = dbManager.prepareStatement(CREATE_USER_SQL);
            preparedStatement.setString(1, right);
            preparedStatement.setString(2, user.getFullName());
            preparedStatement.setDate(3, new Date(user.getDateOfBirth().getTime()));
            preparedStatement.setString(4, user.getLogin());
            preparedStatement.setString(5, user.getPasswordHash());
            preparedStatement.setDate(6, new Date(user.getRegisterDate().getTime()));
            preparedStatement.setInt(7, user.getCountry());
            preparedStatement.executeUpdate();
            dbManager.commit();
        }
        catch (SQLException ex)
        {
            dbManager.rollback();
            throw new DBException(ex.getMessage(), ex);
        }
        finally
        {
            closeStatement(preparedStatement);
        }
    }

    @Override
    public void edit(UserRecord user) throws DBException {
        PreparedStatement preparedStatement = null;
        String right = getStringRight(user.getRight());
        try
        {
            dbManager.commit();
            preparedStatement = dbManager.prepareStatement(UPDATE_USER_SQL);
            preparedStatement.setString(1, user.getFullName());
            preparedStatement.setDate(2, new Date(user.getDateOfBirth().getTime()));
            preparedStatement.setString(3, user.getPasswordHash());
            preparedStatement.setDate(4, new Date(user.getRegisterDate().getTime()));
            preparedStatement.setInt(5, user.getCountry());
            preparedStatement.setInt(6, user.getId());
            preparedStatement.executeUpdate();
            dbManager.commit();
        }
        catch (SQLException ex)
        {
            dbManager.rollback();
            throw new DBException(ex.getMessage(), ex);
        }
        finally
        {
            closeStatement(preparedStatement);
        }
    }

    @Override
    public void remove(int id) throws DBException {
        PreparedStatement preparedStatement = null;
        try
        {
            dbManager.commit();
            preparedStatement = dbManager.prepareStatement(REMOVE_USER_SQL);
            preparedStatement.setInt(1, id);
            preparedStatement.executeUpdate();
            dbManager.commit();
        }
        catch (SQLException ex)
        {
            dbManager.rollback();
            throw new DBException(ex.getMessage(), ex);
        }
        finally
        {
            closeStatement(preparedStatement);
        }
    }

    @Override
    public User find(int id) throws DBException {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        User user = null;
        try
        {
            preparedStatement = dbManager.prepareStatement(FIND_USER_BY_ID_SQL);
            preparedStatement.setInt(1, id);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next())
            {
                user = getUserEntity(resultSet);
            }
        }
        catch (SQLException ex)
        {
            throw new DBException(ex.getMessage(), ex);
        }
        finally
        {
            closeResultSet(resultSet);
            closeStatement(preparedStatement);
        }
        return user;
    }

    @Override
    public List<User> findAll() throws DBException {
        ResultSet resultSet = dbManager.execSQL(FIND_ALL_USERS_SQL);
        List<User> users = new ArrayList<>();
        try
        {
            while (resultSet.next())
            {
                users.add(getUserEntity(resultSet));
            }
        }
        catch (SQLException ex)
        {
            throw new DBException(ex.getMessage(), ex);
        }
        finally {
            closeResultSet(resultSet);
        }
        return users;
    }

    @Override
    public int count() throws DBException {
        int count = 0;
        ResultSet resultSet = null;
        try
        {
            resultSet = dbManager.execSQL(FIND_COUNT_USERS_SQL);
            while (resultSet.next())
            {
                count = resultSet.getInt(1);
            }
        }
        catch (SQLException ex)
        {
            throw new DBException(ex.getMessage(), ex);
        }
        finally {
            closeResultSet(resultSet);
        }
        return count;
    }

    @Override
    public User findByLogin(String login) throws DBException {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        User user = null;
        try
        {
            preparedStatement = dbManager.prepareStatement(FIND_USER_BY_LOGIN_SQL);
            preparedStatement.setString(1, login);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next())
            {
                user = getUserEntity(resultSet);
            }
        }
        catch (SQLException ex)
        {
            throw new DBException(ex.getMessage(), ex);
        }
        finally
        {
            closeResultSet(resultSet);
            closeStatement(preparedStatement);
        }
        return user;
    }

    private User getUserEntity(ResultSet resultSet) throws SQLException, DBException
    {
        User user = new User();
        user.setId(resultSet.getInt(USER_ID_VALUE));
        user.setFullName(resultSet.getString(USER_NAME_VALUE));
        user.setDateOfBirth(resultSet.getDate(USER_BIRTHDAY_VALUE));
        user.setLogin(resultSet.getString(USER_LOGIN_VALUE));
        user.setPasswordHash(resultSet.getString(USER_PASSWORD_VALUE));
        user.setRegisterDate(resultSet.getDate(USER_REGISTER_VALUE));

        String right = resultSet.getString(USER_RIGHT_VALUE);
        if (right.toLowerCase().equals("admin"))
        {
            user.setRight(Right.ADMIN);
        }
        else
        {
            user.setRight(Right.USER);
        }

        user.setCountry(daoFacade.getCountryDAO().find(resultSet.getInt(USER_COUNTRY_VALUE)));
        return user;
    }

    private String getStringRight(Right right)
    {
        if (right == Right.ADMIN)
        {
            return "admin";
        }
        else
        {
            return "user";
        }
    }

    @Override
    public void changePassword(int userID, String hashPassword) throws DBException {
        PreparedStatement preparedStatement = null;
        try {
            dbManager.commit();
            preparedStatement = dbManager.prepareStatement(CHANGE_PASSWORD);
            preparedStatement.setString(1, hashPassword);
            preparedStatement.setInt(2, userID);
            preparedStatement.executeUpdate();
            dbManager.commit();
        } catch (SQLException ex) {
            dbManager.rollback();
            throw new DBException(ex.getMessage(), ex);
        } finally {
            closeStatement(preparedStatement);
        }
    }
   
}
